﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Cit.CDE.Net.Module;
using Module;
using System.Collections.Generic;
using System.Text;
using Cit.CDE.ApplicationBlocks.Data;
using Cit.CDE.Net.Service.DataAccess;
using System.Web.Configuration;




/// <summary>
/// Copyright (C)   jinggubang
/// FileName:        SqlQueryEntity.cs
/// Author :         fengs
/// CDT:             2012-04-011           
/// Version:         1.0.0.0                
/// Depiction:       sql查询
/// </summary>
public class SqlQueryEntity : System.Web.HttpApplication
{
    public SqlQueryEntity()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }
    /// <summary>
    /// 查询条件添加
    /// </summary>
    /// <param name="sc">CDESearchCriteria</param>
    /// <returns></returns>
    private static string conditionChange(CDESearchCriteria sc,string orderFlag)
    {
        //转换基础查询条件
        string searchCondition = string.Empty;

        foreach (object[] con in sc.ListConditions)
        {
            string tiaojian = Util.objectToString(con[2]).Replace("'", "''");

            if (Util.objectToString(con[0]) == "PHONENUMBER") 
            {
                

                searchCondition = searchCondition +
                    " (ORDERS." + Util.objectToString(con[0]) + " like '%" + tiaojian + "%'" +
                    " or PERSONINFO.PHONENUMBER like '%" + tiaojian + "%'" +
                    " or PERSONINFO.PHONENUMBER2 like '%" + tiaojian + "%'" +
                    " or PERSONINFO.CLEWPHONENUMBER like '%" + tiaojian + "%'" +
                    " or PERSONINFO.CLEWMOBILNUMBER like '%" + tiaojian + "%'" +
                    " or PERSONINFO.OTHERCONTACT like '%" + tiaojian + "%')" + " and";
                continue;
            }

            CDEColumn column = sc.EntityManager.getCondition(Util.objectToString(con[0])).getP_Colmun();

            string fieldFullName = " " + column.Table.Name + "." + column.Name;

            switch (Util.objectToString(con[1]))
            {
                case "Equality":

                    string type = column.Type;

                    if (type.Equals("N"))
                    {

                        if (tiaojian.StartsWith(">") || tiaojian.StartsWith("<"))
                        {
                            searchCondition = searchCondition + " convert(decimal," + fieldFullName + ") " + tiaojian + " and";
                        }
                        else if (tiaojian.Contains(":"))
                        {
                            string[] valueList = tiaojian.Split(':');

                            searchCondition = searchCondition + " convert(decimal," + fieldFullName + ") >= " + valueList[0] + " and convert(decimal," + fieldFullName + ") <= " + valueList[1] + " and";
                        }
                        else
                        {
                            searchCondition = searchCondition + " convert(decimal," + fieldFullName + ") = " + tiaojian + " and";
                        }
                    }
                    else
                    {
                        if (tiaojian.StartsWith(">") || tiaojian.StartsWith("<"))
                        {
                            string data = tiaojian;

                            searchCondition = searchCondition + " " + fieldFullName + " " + data.Substring(0, 2) + "'" + data.Substring(2, data.Length - 2) + "'" + " and";
                        }
                        else if (tiaojian.Contains(":"))
                        {
                            string[] valueList = tiaojian.Split(':');

                            searchCondition = searchCondition + " " + fieldFullName + " >= '" + valueList[0] + "' and " + fieldFullName + " <= '" + valueList[1] + "' and";
                        }
                        else
                        {

                            searchCondition = searchCondition + fieldFullName + " = '" + tiaojian + "' and";

                        }
                    }
                    break;

                case "Like":
                    if (fieldFullName == " USERS.USER_NAME")
                    {
                        searchCondition = searchCondition + "'\"'+convert(varchar(20),USERS.id)+'\"' in (select '\"'+convert(varchar(20),id)+'\"' from users                                 where user_name like '%" + tiaojian + "%') and";
                    }
                    else 
                    {
                        searchCondition = searchCondition + fieldFullName + " like '%" + tiaojian + "%' and";
                    }
                    break;

                case "IsNotNull":

                    searchCondition = searchCondition + fieldFullName + " is not null and";
                    break;

                case "Inequality":

                    searchCondition = searchCondition  + fieldFullName + " <> '" + tiaojian + "' and";
                    break;
                case "LikeLeft":
                    if (fieldFullName.EndsWith("BANK.BANK_ID"))
                    {
                        searchCondition = searchCondition + " orders.bankname in (select  convert(varchar(20),id) from bank where " + fieldFullName + " like '" + tiaojian + "%') and";
                    }
                    else
                    {
                        searchCondition = searchCondition + fieldFullName + " like '" + tiaojian + "%' and";
                    }
                    break;
                case "Include":
                    searchCondition = searchCondition + fieldFullName + " in (" + tiaojian + ") and";
                    break;
                default:
                    break;
            }
        }
        if (sc.EntityManagerName == "WEB.ORDERS_MANAGER" || (sc.EntityManagerName == "WEB.ORDER_MANAGER_OUTPUT" && orderFlag == "ORDERS_MANAGER")) 
        {
            searchCondition = searchCondition + " ORDERS.ORDERSTATE in (select convert(varchar(20),id) from DUNSTATE where DUNSTATE.STATECODE not in ('100002','100003','180001','220006','220007','220008')) and";

           // searchCondition = searchCondition + " CONVERT(FLOAT,ISNULL(RECKONING.ARREARSTOTAL,0)) > 0 and";

        }
        if (searchCondition.EndsWith("and"))
        {
            searchCondition = searchCondition.Substring(0, searchCondition.Length - 4);
        }

        return searchCondition;

    }


    /// <summary>
    /// 条件查询
    /// </summary>
    /// <param name="em">EntityManager</param>
    /// <param name="sc">CDESearchCriteria</param>
    /// <returns></returns>
    public static List<CDEEntity> sqlQueryList(CDEEntityManager em, CDESearchCriteria sc)
    {
        //转换排序条件
        string sort = string.Empty;

        if (sc.ListSortOders.Count > 0)
        {
            sort = " order by ";



            foreach (object[] obj in sc.ListSortOders)
            {
                string type = string.Empty;

                type = sc.EntityManager.getAttribute(obj[0].ToString()).getP_Colmun().Type;

                if (type == "N")
                {
                    sort = sort + "convert(decimal,ORDERS." + Util.objectToString(obj[0]) + ") " + Util.objectToString(obj[1]) + ",";
                }
                else
                {
                    sort = sort + Util.objectToString(obj[0]) + " " + Util.objectToString(obj[1]) + ",";
                }
            }
        }

        if (sort.EndsWith(","))
        {
            sort = sort.Substring(0, sort.Length - 1);
        }
        //转换分页条件
        string top = string.Empty;

        int cou = int.MinValue;

        if (sc.PageSize == 0)
        {
            top = " ";
        }
        else
        {
            cou = sc.PageSize + sc.PageStart - 1;

            top = " top " + cou.ToString() + " ";
        }
        //查询字段
        string clop = string.Empty;

        foreach (CDEAttribute cat in em.ListAttributes)
        {
            if (cat.Name == "ARREARSTOTAL")
            {
                clop = clop + "RECKONING.ARREARSTOTAL as " + cat.Name + ",";
            }
            else if (cat.Name.Contains("__"))
            {
                clop = clop + cat.getP_Colmun().Table.Name + "." + cat.getP_Colmun().Name + " as " + cat.Name + ",";
            }
            else
            {
                clop = clop + "ORDERS." + cat.Name + " as " + cat.Name + ",";
            }
            
        }
        StringBuilder sql = new StringBuilder();
        sql.AppendLine("select ");
        sql.AppendLine(top);
        sql.AppendLine(clop);
        sql.AppendLine("ORDERS.ID CDE_DATAROW_ID ");
        sql.AppendLine(" from ORDERS left join PERSONINFO on ORDERS.PERSONINFO_ID = PERSONINFO.ID ");
        sql.AppendLine("left join RATINGSTANDARD on ORDERS.CREDENCE_LEVEL = RATINGSTANDARD.ID ");
        sql.AppendLine("left join  CATEGORIES on ORDERS.STAGESTATE = CATEGORIES.ID");
        sql.AppendLine("left join USERS ON ORDERS.ACCNUMBER = USERS.ID ");
        sql.AppendLine("left join CITYKEY ON PERSONINFO.ADDRESSNUMBER = CITYKEY.ID");
        sql.AppendLine("left join CONTRACT_BATCH ON CONTRACT_BATCH.ID = ORDERS.BATCH");
        sql.AppendLine("left join BANK ON ORDERS.BANKNAME = BANK.ID");
        sql.AppendLine("left join RECKONING on ORDERS.PACTCONNECTNUMBER = RECKONING.PACTCONNECTNUMBER AND RECKONING.ISCURRENT = '0'");
        sql.AppendLine("left join DUNSTATE on ORDERS.ORDERSTATE = DUNSTATE.ID where ");
        sql.AppendLine(conditionChange(sc,""));
        sql.AppendLine(sort);

        DataSet ds = new DataSet();

        SQLHelper.FillDataset(DataAccessManager.connStr, CommandType.Text, sql.ToString(), ds, "table");

        List<CDEEntity> surveyList = new List<CDEEntity>();

        DataTable dt = ds.Tables["table"];

        for (int i = 0; i < sc.PageStart - 1; i++)
        {
            dt.Rows.RemoveAt(0);
        }
        //重构entity
        foreach (DataRow dr in dt.Rows)
        {
            CDEEntity entity = new CDEEntity(em, dr);

            surveyList.Add(entity);
        }
        return surveyList;
    }
    /// <summary>
    /// 查询总条数
    /// </summary>
    /// <param name="sc">CDESearchCriteria</param>
    /// <returns></returns>
    public static int sqlQueryListCount(CDESearchCriteria sc)
    {
        StringBuilder sql = new StringBuilder();

        sql.AppendLine("select count(*) from ORDERS left join PERSONINFO on ORDERS.PERSONINFO_ID = PERSONINFO.ID ");
        sql.AppendLine("left join RATINGSTANDARD on ORDERS.CREDENCE_LEVEL = RATINGSTANDARD.ID ");
        sql.AppendLine("left join  CATEGORIES on ORDERS.STAGESTATE = CATEGORIES.ID");
        sql.AppendLine("left join USERS ON ORDERS.ACCNUMBER = USERS.ID ");
        sql.AppendLine("left join CITYKEY ON PERSONINFO.ADDRESSNUMBER = CITYKEY.ID");
        sql.AppendLine("left join CONTRACT_BATCH ON CONTRACT_BATCH.ID = ORDERS.BATCH");
        sql.AppendLine("left join BANK ON ORDERS.BANKNAME = BANK.ID");
        sql.AppendLine("left join RECKONING on ORDERS.PACTCONNECTNUMBER = RECKONING.PACTCONNECTNUMBER AND RECKONING.ISCURRENT = '0'");
        sql.AppendLine("left join DUNSTATE on ORDERS.ORDERSTATE = DUNSTATE.ID where ");
        sql.AppendLine(conditionChange(sc,""));

        DataSet ds = new DataSet();

        SQLHelper.FillDataset(DataAccessManager.connStr, CommandType.Text, sql.ToString(), ds, "table");

        List<CDEEntity> surveyList = new List<CDEEntity>();

        DataTable dt = ds.Tables["table"];

        return int.Parse(Util.objectToString(dt.Rows[0].ItemArray[0]));
    }
    /// <summary>
    /// 导出——包含调查信息条件查询
    /// </summary>
    /// <param name="em">EntityManager</param>
    /// <param name="sc">CDESearchCriteria</param>
    /// <returns></returns>
    public static List<CDEEntity> sqlQueryListExc(CDEEntityManager em, CDESearchCriteria sc,string orderFlag)
    {
        //转换排序条件
        string sort = string.Empty;

        if (sc.ListSortOders.Count > 0)
        {
            sort = " order by ";



            foreach (object[] obj in sc.ListSortOders)
            {
                string type = string.Empty;

                type = sc.EntityManager.getAttribute(obj[0].ToString()).getP_Colmun().Type;

                if (type == "N")
                {
                    sort = sort + "convert(decimal,ORDERS." + Util.objectToString(obj[0]) + ") " + Util.objectToString(obj[1]) + ",";
                }
                else
                {
                    sort = sort + Util.objectToString(obj[0]) + " " + Util.objectToString(obj[1]) + ",";
                }
            }
        }

        if (sort.EndsWith(","))
        {
            sort = sort.Substring(0, sort.Length - 1);
        }
        //转换分页条件
        string top = string.Empty;

        int cou = int.MinValue;

        if (sc.PageSize == 0)
        {
            top = " ";
        }
        else
        {
            cou = sc.PageSize + sc.PageStart - 1;

            top = " top " + cou.ToString() + " ";
        }
        //查询字段
        string clop = string.Empty;

        foreach (CDEAttribute cat in em.ListAttributes)
        {
            if (cat.Name == "ARREARSTOTAL")
            {
                clop = clop + "RECKONING.ARREARSTOTAL as " + cat.Name + ",";
            }
            else if (cat.Name.Contains("__"))
            {
                clop = clop + cat.getP_Colmun().Table.Name + "." + cat.getP_Colmun().Name + " as " + cat.Name + ",";
            }
            else if (cat.Name == "FELLBACKDAY")
            {
                clop = clop + "RECKONING.FELLBACKMONTH as " + cat.Name + ",";
            }
            else
            {
                clop = clop + "ORDERS." + cat.Name + " as " + cat.Name + ",";
            }

        }
        StringBuilder sql = new StringBuilder();
        sql.AppendLine("select ");
        sql.AppendLine(top);
        sql.AppendLine(clop);
        sql.AppendLine("ORDERS.ID CDE_DATAROW_ID,DUNSTATE.STATECODE ORDERSTATE__STATECODE");
        sql.AppendLine(" from ORDERS left join PERSONINFO on ORDERS.PERSONINFO_ID = PERSONINFO.ID ");
        sql.AppendLine("left join RATINGSTANDARD on ORDERS.CREDENCE_LEVEL = RATINGSTANDARD.ID ");
        sql.AppendLine("left join  CATEGORIES on ORDERS.STAGESTATE = CATEGORIES.ID");
        sql.AppendLine("left join USERS ON ORDERS.ACCNUMBER = USERS.ID ");
        sql.AppendLine("left join CITYKEY ON PERSONINFO.ADDRESSNUMBER = CITYKEY.ID");
        sql.AppendLine("left join CONTRACT_BATCH ON CONTRACT_BATCH.ID = ORDERS.BATCH");
        sql.AppendLine("left join BANK ON ORDERS.BANKNAME = BANK.ID");
        sql.AppendLine("left join RECKONING on ORDERS.PACTCONNECTNUMBER = RECKONING.PACTCONNECTNUMBER AND RECKONING.ISCURRENT = '0'");
        sql.AppendLine("left join DUNSTATE on ORDERS.ORDERSTATE = DUNSTATE.ID where ");
        sql.AppendLine(conditionChange(sc,orderFlag));
        sql.AppendLine(sort);

        DataSet ds = new DataSet();

        SQLHelper.FillDataset(DataAccessManager.connStr, CommandType.Text, sql.ToString(), ds, "table");

        List<CDEEntity> surveyList = new List<CDEEntity>();

        DataTable dt = ds.Tables["table"];

        //for (int i = 0; i < dt.Rows.Count - 1; i++)
        //{
        //    if (dt.Rows[i][0].Equals(dt.Rows[i + 1][0]))
        //    {
        //        dt.Rows.RemoveAt(i);

        //        i--;
        //    }
        //}
        for (int i = 0; i < sc.PageStart - 1; i++)
        {
            dt.Rows.RemoveAt(0);
        }
        //得到银行code和名称，并组成map,键值为银行的id
        Dictionary<string, string> mapBank=getBankNameMap();
        //Dictionary<string, string> map = Util.getMapOfDunState("$");
        ////重构entity
        //foreach (DataRow dr in dt.Rows)
        //{
        //    string statecode = Util.objectToString(dr["ORDERSTATE__STATECODE"]);
        //    dr["ORDERSTATE__STATENAME"] = map.ContainsKey(statecode) == true ? map[statecode] : "";
        //    string bankId = Util.objectToString(dr["BANKNAME"]);
        //    if (mapBank .ContainsKey(bankId))
        //    {
        //        dr["BANKNAME__BANK_NAME"] = mapBank[bankId] +"$"+ Util.objectToString(dr["BANKNAME__BANK_NAME"]);
        //    }            
        //    CDEEntity entity = new CDEEntity(em, dr);

        //    surveyList.Add(entity);
        //}
        return surveyList;
    }
    /// <summary>
    /// 得到银行code和名称，并组成map，键值为银行的id,value为上级银行名称
    /// </summary>
    private static Dictionary<string,string> getBankNameMap()
    {
        Dictionary<string, string> mapBank = new Dictionary<string, string>();
        Dictionary<string, string> mapIdBankName = new Dictionary<string, string>();
        string sql = "SELECT * FROM BANK";
        DataTable table =Util .loadSql(sql);
        if (table.Rows.Count > 0)
        {
            //组装code,name map
            foreach (DataRow  row in table .Rows)
            {
                string code = Util.objectToString(row["BANK_ID"]);
                string name = Util.objectToString(row["BANK_NAME"]);
                if (code != "" && !mapBank.ContainsKey(code))
                {
                    mapBank.Add(code, name);
                }
            }
            //组装id,上级银行name map
            foreach (DataRow row in table.Rows)
            {
                //上级银行编号
                string topCode = Util.objectToString(row["TOP_BANK_ID"]);
                string id =Util .objectToString( row["ID"]);
                if (topCode != "" && mapBank.ContainsKey(topCode) &&　!mapIdBankName.ContainsKey(id))
                {
                    mapIdBankName.Add(id, Util.objectToString(mapBank[topCode]));
                }
            }
        }
        return mapIdBankName;
    }

     
    /// <summary>
    /// 取得用户权限查询SQL
    /// </summary>
    public static string getUserLoreSql(LoginOprator op){ 
        //LORE H2342646
        //LORE_TYPE H1086874462
        //USERS H1606993344
        //AREA H1307872231
        //Cit.CDE.Net.Module.CDECriteria lore_type = new CDECriteria("LORE_TYPE__TYPE_CODE", CDEConstCompare.LikeLeft, "");
        //list_Criteria.Add(lore_type);

        // 资料表CODE
        //10：产品部
        //11：市场部
        //12：培训部
        // 部门ID  1:产品部 2:市场部 3:销售部 4:管理部 5:培训部
        string dept_id = op.DEPT_ID;
        //责任区域
        string area_id = op.AREA_ID;
        //0:员工 1：主管
        string post = op.POST;
        StringBuilder sql = new StringBuilder();

        if (dept_id == "3")
        {  //销售 
            //员工
            if (post == "0")
            {
                sql.AppendFormat(@" (H1086874462.TYPE_CODE LIKE '10%') 
	        OR (H1086874462.TYPE_CODE LIKE '11%' AND H2342646.AREA_ID='{0}')
	        OR (H1086874462.TYPE_CODE LIKE '12%' AND H2342646.AREA_ID='{0}')", area_id);
            }
            else
            { //主管
                sql.AppendFormat(@" (H1086874462.TYPE_CODE LIKE '10%') 
	        OR (H1086874462.TYPE_CODE LIKE '11%')
	        OR (H1086874462.TYPE_CODE LIKE '12%')");
            }
        }
        else if (dept_id == "2")  //市场
        {
            //员工
            if (post == "0")
            {
                sql.AppendFormat(@" (H1086874462.TYPE_CODE LIKE '10%') 
	        OR (H1086874462.TYPE_CODE LIKE '11%' AND H2342646.AREA_ID='{0}')", area_id);
            }
            else
            { //主管
                sql.AppendFormat(@" (H1086874462.TYPE_CODE LIKE '10%') 
	        OR (H1086874462.TYPE_CODE LIKE '11%')");
            }
        }
        else if (dept_id == "5")  //培训
        {
            //员工
            if (post == "0")
            {
                sql.AppendFormat(@" (H1086874462.TYPE_CODE LIKE '10%') 
	        OR (H1086874462.TYPE_CODE LIKE '12%' AND H2342646.AREA_ID='{0}')", area_id);
            }
            else
            { //主管
                sql.AppendFormat(@" (H1086874462.TYPE_CODE LIKE '10%') 
	        OR (H1086874462.TYPE_CODE LIKE '12%')");
            }
        }
        else if (dept_id == "1")  //产品
        {
            sql.AppendFormat(@" (H1086874462.TYPE_CODE LIKE '10%')");
        }

        return sql.ToString();
    }
}
